Functions and Operators Library

The Functions, Operators and Global Variables Library provides end users with an accessible and comprehensive list of supported functions and operators in DEACOM. These functions and operators allow for quick and easy changes in Triggers, User Calcs, Reports, Part Forms, Query Tools, Grid Management and EDI Processing, maximizing your ability to manage data in DEACOM .

The list below contains information on the functions, operators and global variables supported within DEACOM. Users may reference the following website, Microsoft Visual FoxPro Functions & Operators if additional information on the functions and operators is required.

Functions library

Note: The "INCREMENT" and "GRIDSUM" functions were created by and are maintained by Deacom. Information regarding these functions is available following this section.

Function	Format	Description
ABS()	ABS(nExpression)	Returns the absolute value of the specified numeric expression.
ALLTRIM() or ALLT()	ALLTRIM(Expression (, nFlags) (, cParseChar (, cParseChar2 (, ...))))	Removes all leading and trailing spaces or parsing characters from the specified character expression, or all leading and trailing zero (0) bytes from the specified binary expression.
AT()	AT(cSearchExpression, cExpressionSearched (, nOccurrence))	Searches a character expression for the occurrence of another character expression.
BETWEEN()	BETWEEN(eTestValue, eLowValue, eHighValue)	Determines whether the value of an expression is inclusively between the values of two expressions of the same type.
CAST()	CAST(eExpression AS cDataType ((,nFieldWidth (, nPrecision)))(NULL / NOT NULL))	Converts an expression from one data type to another.
CDOW()	CDOW(dExpression / tExpression)	Returns the day of the week from a given Date or DateTime expression.
CEILING	CEILING(nExpression)	Returns the next highest integer that is greater than or equal to the specified numeric expression.
CHR()	CHR(nANSICode)	Returns the character associated with the specified numeric ANSI code.
CMONTH()	CMONTH(dExpression / tExpression)	Returns the name of the month from a given date or DateTime expression.
CTOD()	CTOD(cExpression)	Converts a character expression to a date expression.
DATE ()	DATE((nYear, nMonth, nDay))	Returns the current system date, which is controlled by the operating system, or creates a year 2000-compliant Date value.
DAY()	DAY(dExpression / tExpression)	Returns the numeric day of the month for a given Date or DateTime expression.
DMY()	DMY(dExpression / tExpression)	Returns a character expression in day-month-year format (for example, 31 May 1998) from a Date or DateTime expression. The month name isn't abbreviated.
DOCEXISTS()	DOCEXISTS(cTable, nRecord, cExpression)	Determines whether a document is attached to the record for the specified table.
DOW()	DOW(dExpression / tExpression (, nFirstDayOfWeek))	Returns a numeric day-of-the-week value from a Date or DateTime expression.
DTOC()	DTOC(dExpression / tExpression (, 1))	Returns a Character-type date from a Date or DateTime expression.
DTOS()	DTOS(dExpression / tExpression)	Returns a character-string date in a yyyy/mm/dd format from a specified Date or DateTime expression.
EMPTY()	EMPTY(eExpression)	Determines whether an expression evaluates to empty. Replaces the no longer supported ISBLANK() function.
EVALUATE()	EVALUATE(cExpression)	Evaluates a character expression and returns the result.
F_BARCODE()	F_BARCODE(f_Fontname, f_Fontsize, f_Expression)	Creates a barcode for the given expression that is readable by the DEACOM Warehouse Management System. Note that this expression must be used as a top-level expression. This means that it cannot be used within another expression (IIF(F_BARCODE(...),...,...). F_BARCODE's role is to format an expression rather than apply logic to an expression the way a traditional function does.
FLOOR()	FLOOR(nExpression)	Returns the nearest integer that is less than or equal to the specified numeric expression.
GETVAR()	GETVAR ("variable name")	Returns the value of a specified system variable, the name of which must be in quotes ("name"). This function returns a string containing the value stored in the variable if everything works fine, an empty string if the variable name does not exist, and invalid if the variable name is of the wrong syntax (for example, it contains forbidden characters).
IIF()	IIF(lExpression, eExpression1, eExpression2)	Returns one of two values depending on the value of a logical expression.
INLIST()	INLIST(eExpression1, eExpression2 (, eExpression3 ...))	Determines whether an expression matches another expression in a set of expressions.
ISALPHA()	ISALPHA(cExpression)	Determines whether the leftmost character in a character expression is alphabetic.
ISDIGIT()	ISDIGIT(cExpression)	Determines whether the leftmost character of the specified character expression is a digit (0 through 9).
LEFT()	LEFT(cExpression, nExpression)	Returns a specified number of characters from a character expression, starting with the leftmost character.
LEN()	LEN(cExpression)	Determines the number of characters in a character expression, indicating the length of the expression.
LIKE()	LIKE(cExpression1, cExpression2)	Determines if a character expression matches another character expression.
LOWER()	LOWER(cExpression)	Returns a specified character expression in lowercase letters.
MAX()	MAX(eExpression1, eExpression2 (, eExpression3 ...))	Evaluates a set of expressions and returns the expression with the maximum value.
MIN()	MIN(eExpression1, eExpression2 (, eExpression3 ...))	Evaluates a set of expressions and returns the expression with the minimum value.
MOD()	MOD(nDividend, nDivisor)	Divides one numeric expression by another numeric expression and returns the remainder.
MONTH()	MONTH(dExpression / tExpression)	Returns the number of the month for a given Date or DateTime expression.
PADL()PADR()PADC()	PADx(eExpression, nResultSize (, cPadCharacter))(x = L or R or C)	Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
PROPER()	PROPER(nExpression)	Returns a string expression with the first letter of each word capitalized.
RAT()	RAT(cSearchExpression, cExpressionSearched (, nOccurrence))	Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
RECNO()	RECNO((nWorkArea / cTableAlias))	Returns the current record number in the current or specified table.
RIGHT()	RIGHT(cExpression, nCharacters)	Returns the specified number of rightmost characters from a character string.
ROUND()	ROUND(nExpression, nDecimalPlaces)	Returns a numeric expression rounded to a specified number of decimal places.
SETVAR()	SETVAR ("(variable name)", (value))	Sets the specified value to the specified variable, the name of which must be in quotes ("name").
SPACE()	SPACE(nSpaces)	Returns a character string composed of a specified number of spaces.
SQRT()	SQRT(nExpression)	Returns the square root of the specified numeric expression.
STR()	STR(nExpression (, nLength (, nDecimalPlaces)))	Returns the character equivalent of a numeric expression.
STRTRAN()	STRTRAN(cSearched, cExpressionSought (, cReplacement) (, nStartOccurrence) (, nNumberOfOccurrences) (, nFlags))	Searches a character expression or memo field for a second character expression or memo field and replaces each occurrence with a third character expression or memo field. You can specify where the replacement begins and how many replacements are made.
SUBSTR()	SUBSTR(cExpression, nStartPosition (, nCharactersReturned))	Returns a character string from the given character expression or memo field, starting at a specified position in the character expression or memo field and continuing for a specified number of characters.
TIME()	TIME()	Returns the current system time in 24-hour, eight-character string (hh:mm:ss) format.
TRANSFORM()	TRANSFORM(eExpression, (cFormatCodes))	Returns a character string from an expression in a format determined by a format code.
TRIM()	TRIM(cExpression (, nFlags) (, cParseChar (, cParseChar2 (, ...))))	Removes all trailing spaces or parsing characters from the specified character expression, or all trailing zero (0) bytes from the specified binary expression.
TTOC()	TTOC(tExpression (, 1 / 2 / 3))	Converts a DateTime expression to a Character value with the specified format.
LTRIM()	LTRIM(Expression (, nFlags) (, cParseChar (, cParseChar2 (, ...))))	Removes all leading spaces or parsing characters from the specified character expression, or all leading zero (0) bytes from the specified binary expression.
RTRIM()	RTRIM(Expression (, nFlags) (, cParseChar (, cParseChar2 (, ...))))	Removes all trailing spaces or parsing characters from the specified character expression, or all leading zero (0) bytes from the specified binary expression.
UPPER()	UPPER(cExpression)	Returns the specified character expression in uppercase.
VAL()	VAL(cExpression)	Returns a numeric or currency value from a character expression composed of numbers.
YEAR()	YEAR(dExpression / tExpression)	Returns the year from the specified date or datetime expression.

Please be aware that the Format column in this table provides optional formats for some functions, but they are not required. Optional formats are signified by brackets([]).

For example, the format of ALLTRIM is listed in the Functions Library as:

ALLTRIM(Expression [, nFlags] [, cParseChar [, cParseChar2 [, ...]]])

To use ALLTRIM, the required format is simply:

ALLTRIM(Expression)

The additional formats are represented by:

[, nFlags] [, cParseChar [, cParseChar2 [, ...]]])

Please note that adding any of the additional parameters available in the Format section must be listed as a sequential comma-separated list. If a user wanted to include a cParseChar in their ALLTRIM, they would add it onto the required format as follows:

ALLTRIM(Expression[, cParseChar])

The [, ...] in [, cParseChar [, cParseChar2 [, ...]]indicates that a user can include as many cParseChar selections in their format as allowed by the function. If a user wanted to select 3 cParseChar options in their format, it would read as:

ALLTRIM(Expression[, cParseChar [, cParseChar2 [, cParseChar3])

BYTESTOSTRING function

The BYTESTOSTRING function was created and is maintained by Deacom. The purpose of this special function is to read the eh_msg text that results from running the EDISeparator external program. This function should be usable in the Trigger "Message" field.This function is able to parse the dxedihistory.eh_msg field from bytes into a larger string.Example: <<BYTESTOSTRING(eh_msg)>>

INCREMENT function

As indicated previously, the INCREMENT function was created by and is maintained by Deacom. The INCREMENT function maintains a counter and returns the next sequential number every time it’s called. It takes two parameters, the second one is optional:

• Counter name = If the counter name doesn’t exist in DXPSET, it gets created with a value of one, otherwise it gets incremented by one.
• Number of digits = If not specified, it defaults to five. The counter will be incremented until it runs out of numbers and then reset back to one, so if six digits are specified, the counter will go up to 999,999 before rolling over to 1.

TIP: How would you add an incrementing number to each label starting from one? Example: if you are trying to print 100 labels the first one would have the number 1, last one would have 100. The field to use on the part form is “LN_COPY”

INCREMENT Function Examples

These examples are geared towards pre-printing job labels for production but the concept can be used elsewhere.

• Customer needs a number to increment at the end of the job number but reset each time they print: RIGHT(TRANSFORM(jo_jobnum, "9999-99999"), 5) + "-" + TRANS(VAL(ln_copy),"@L 9999")
• Customer needs a number to increment at the end of the job number but also wants to indicate a starting number in case they need to reprint a range or start at a new number for the next production batch for a different shift/day: RIGHT(TRANSFORM(jo_jobnum, "9999-99999"), 5) + "-" + TRANS(VAL(ln_copy) + (VAL(labeluser1) - 1), "@L 9999")

In the example above, label user 1 is entered at the time of printing. Navigate to Production > Job Reporting > View > Double click job > Click Job Labels at the top of the form > choose printer > click Print > double click the label in the grid > enter copies to print and the starting number in the “Label User 1” field.

This function can also be used on Report Layouts to indicate if the document has already been printed. It may require some customization depending on the application.

This does three things:

• Purple components create the input argument for the INCREMENT function, which takes one argument: INCREMENT(argument). Input is composed of:
  • (jobnum) + (the characters after the right-most occurrence of the '_' character in pr_codenum) + (r_pageno, in a 3-digit number format using 0's to fill in, e.g. '1' becomes '001')
  • This argument can be found in dxpset. It is the value in ps_fldname (without the preceding ‘u_’), and whatever is put into the argument for INCREMENT is here.
    • Every time it is a unique argument input to INCREMENT, a new entry to ps_fldname in dxpset is made.
    • Every time an existing product of the INCREMENT function is called, instead of creating a new entry it makes the ps_number field increment up by 1. Therefore, for every time the argument in the INCREMENT function generates an output that exists already in ps_fldname, the corresponding ps_number increases by one.
    • Important: This is for every time INCREMENT is called. If INCREMENT is used in two different fields of a report or part form, then it is called two times. Therefore, ps_number will increase by 2 for every 1 time that part form is processed.
• Highlighted components take the returned increment (ps_number value) and subtracts 1 from it. This is because the first print is the original, not a re-print. Therefore, the second print would be the first re-print.
• Green components enable the "reprint #" text.

GRIDSUM function

The GRIDSUM function allows users to first filter data within the grid and then create expressions to sum the results.

The GRIDSUM function signature is as follows:

GRIDSUM(pc_Expression , pc_Filter)

• The parameter pc_Expression is a string expression to be evaluated. The result of the evaluation of pc_Expression will be summed.
• The parameter pc_Filter is an optional string expression. If an expression for pc_Filter is provided, it will be evaluated against all of the data and only the applicable rows will have pc_Expression evaluated against them and summed.

GRIDSUM examples

View BOM Grid expression

An example of the GRIDSUM function might be: GRIDSUM("totwgt", "ALLT(pr_codenum) = 'RAW1' OR ALLT(pr_codenum) = 'RAW2'"). In this case, the GRIDSUM example was added to a user field in the DEACOM Viewbom grid.

Sales Trends- Units Report - Percentage change in units sold this year versus the same period last year - by part number

• Add a new column to the Sales Trends – Units report in Sales > Order Reporting via the grid layout button to display the Edit Grid Column form.
• Click the Add button.
• Change Field value to "User-Defined"
• Enter the following in the User Expression field: (totalsales-GRIDSUM("totalsales", "pr_id = " + ALLT(STR(pr_id)) + " AND Year='" + ALLT(STR(VAL(year) - 1)) + "'"))/GRIDSUM("totalsales", "pr_id = " + ALLT(STR(pr_id)) + " AND Year='" + ALLT(STR(VAL(year) - 1)) + "'")*100
• Change the Title field to say "% Diff"
• Start with a width of 10.
• Ensure the "Include in preview" box is checked.
• Save the form when complete.
• Re run the Sales Trends - Units Report
• Notice the percent difference from the previous year is displayed in the appropriate column.

Additional functions

Examples of additional functions that may be used within DEACOM, including report design, are included below.

• F_TIME() is for database time and TIME() is used for computer time.
• F_DATE() is used for the database date and DATE() is used for the computer date.
• CONV24TO12(F_TIME()) is used to change times in military format to a 12 hour format and includes AM/PM. (Example: CONV24TO12(m.co_nexttime) )

Operators library

Arithmetic Operators

Operator	Format	Action	Parameters
+	x1 + x2  or x1 + x1	Adds two numbers or returns the positive value of a numeric expression. Can also be used to concatenate two string expressions.	expression1 -Required. Any numeric or string expression.  expression2 - Required unless the + operator is calculating a negative value. Any numeric or string expression.  DEACOM Example - allt(trans(or_linenum + 3,"9999"))
-	x1 - x2  or x1 - x1	Returns the difference between two numeric expressions or the negative value of a numeric expression.	expression1 - Required. Any numeric expression.  expression2Required unless the – operator is calculating a negative value. Any numeric expression.
*	x1 * x2	Multiplies two numbers.	expression1 - Required. Any numeric expression.  expression2 - Required. Any numeric expression.
/	x1 / x2	Divides two numbers and returns a floating-point result.	expression1 - Required. Any numeric expression. expression2 - Required. Any numeric expression.
^	number ^ exponent	Raises a number to the power of another number.	number - Required. Any numeric expression. exponent - Required. Any numeric expression.
%	nDividend % nDivisor	Returns the remainder (modulus) obtained by dividing one numeric expression into another.	nDividend - Specifies the dividend (numeric expression being divided). The number of decimal places in nDividend determines the number of decimal places in the result. nDivisor - Specifies the divisor (the numeric expression dividing the dividend nDividend). A positive number is returned if nDivisor is positive; a negative number if nDivisor is negative. nDivisor cannot be zero.

Relational operators

Operator	Action	Example
<	Less than	23 < 54
>	Greater than	1 > 2
=	Equal to	cVar1 = cVar
<>	Not equal to	.T. <> .F.
<=	Less than or equal to	{^1998/02/16} <= {^1998/02/16}
>=	Greater than or equal to	32 >= nHisAge

Additional operators

Operator	Format	Action
OR, ||	logical|| logical or expression	expression or logical	expression or expression	logical	The logical OR operator (||) returns the boolean value true if either or both operands is true and returns false otherwise.  Example: IIF(lo_name="None" OR lo_name="Test", "See Manager", "OK")
AND, &&	expression && expression	The logical AND operator (&&) returns the boolean value true if both operands are true and returns false otherwise.  Example: IIF(lo_name="None" AND fi_expires <= date(), "See Manager", "OK")
!	result = !expressionor result = Not expression	Performs logical negation on a Boolean expression, or bitwise negation on a numeric expression.  Example: !TRUE = FALSE

Formatting

• Dates must be entered as {^yyyy/mm/dd} or {mm/dd/yyyy}.
• Numeric expressions can be represented by natural (whole) or real (fractional) numbers, but can not contain commas. If a number is real, it must be entered in decimal format or scientific notation. For example, the number 3000 can not be entered as 3,000.
• True is represented by .T. and False is represented by .F.. Both .T. and .F. are Boolean values. Using a shipping facility as an example, these logical values represent whether or not an order has been filled. If the order has not been filled, the value would be false. If the order has been filled, the value would be true.

Examples

Type	Use Case	Sample Statement
Embedded IIF() Statements	Used when multiple conditions exist that each contain independent results. Similar to IF / ELSE IF / ELSE statements in programming.	IIF(expression 1, [if expression 1 is true], IIF(expression 2, [if expression 2 is true], [if both expression 1 and expression 2 are false)) e.g. IIF(pr_user9 = 1, 'None', IIF(pr_user9 = 5, 'LP', IIF(pr_user9 = 3, 'BP', ' ')))

DEACOM date functions

The following functions are available to use with user expressions on part forms in DEACOM.

• ADDHOURS(ptDateTime, pnHours)
• ADDMINUTES(ptDateTime, pnMinutes)
• ADDSECONDS(ptDateTime, pnSeconds)

Examples of how these functions would work is illustrated below using the following expressions in a lot label.

<<TTOC(m.fi_recdate)>>

<<ADDHOURS(m.fi_recdate, 1)>>

<<ADDMINUTES(m.fi_recdate, 30)>>

<<ADDMINUTES(m.fi_recdate, 120)>>

In the example above, the functions would perform the actions indciated below:

• ADDHOURS would add 1 hour to the initial time.
• ADDMINUTES would add 30 minutes to the initial time.
• ADDSECONDS would add 120 seconds to the initial time.

Rich Text Formatting

For fields of type "Text" and/or "Expression", DEACOM supports Rich Text including Bold, Italics, Strikethrough, and Underline.

• Bold: [b] {text} [/b]
• Italic: [i] {text} [/i]
• Strikethrough: [s] {text} [/s]
• Underline: [u] {text} [/u]

Similar to entering other Text in an Expression field, make sure to place quotations (" ") around the Text you wish to be displayed.

• Example: "[b] Please RSVP by 10/21 [/b]" will display the sentence "Please RSVP by 10/21".

DEACOM CHILDLOT function

The "CHILDLOT()" function is available for use in the Lot # Expression field in Inventory > Options. This function works in conjunction with the "Propagate user lot" flag on the Edit BOM Line form when editing a bill of materials. The purpose of this feature is to allow the user lot number from the bulk material used in production to carry over to the finished good when finishing production. For example, let's assume we have a finished good of a 5 gallon can of paint. The bill of materials for this 5 gallon can of paint includes the can, lid, label and finally, the paint itself which is the bulk material. Assume we want to have the user lot of the paint which was consumed in the production process (either via issuing or backflushing) carry over to the final product, namely the 5 gallon of paint.

To accomplish this, we first need to check the "Propagate user lot" flag on the bulk material (paint) on the BOM for the finished good (5 gallon can of paint). Next, we need to place, at a minimum, the following value in this field, CHILDLOT(). When quantities of the finished good are produced and placed into inventory or QC, the user lot number of the finished lot will be the same as the user lot number of the bulk material that was issued to the job. Additional fields and/or expressions can be used within the brackets of the CHILDLOT() statement.

• Note: The CHILDLOT feature is designed to be used in situations where bulk materials or subassemblies are made on seperate jobs then the finished goods. In this way, the bulk material or subassembly will already be available in inventory with a lot number. When this lot is issued to the finished good job or a lot is consumed, via backflushing, to the finished good job then the lot number will be carried over to the finished goods that are placed in inventory.

Global variables

Global variables may be used in expressions throughout the system, including grids, reports and part forms. A list of the global variables is provided below.

Variable	Description
go.reldate	Date of last DEACOM update
go.copyright	DEACOM copyright information
go.build	DEACOM version number
go.inifile	File path of INI file
go.reports	File path to Reports folder
go.addins	File path to Addins folder
go.controls	File path to Controls folder
go.help	File path to Help folder
go.cubes	File path to Cubes folder
go.datasrc	"DATASRC" as defined in INI file
go.server	"SERVER" as defined in INI file
go.sysdb	"DATABASE" as defined in INI file
go.servaddr	"SVCADDR" as defined in INI file
go.servport	"SVCPORT" as defined in INI file
go.currdir	File path to Current Directory
go.listenerpath	File path to listener
go.listenerfound	Indicates if listener was found
go.pinpadsettingid	"Pin Pad Com Port" as defined in File > Settings
go.compname	"Company" name as defined in System > Options > General tab
go.posmask	"POS Decimal" as defined in System > Options > General tab
go.pricmask	"Pricing Decimals" as defined in System > Options > General tab
go.quanmask	"Quantity Decimals" as defined in System > Options > General tab
go.bommask	"BOM Decimals" as defined in System > Options > General tab
go.salemask	"Sales Decimals" as defined in System > Options > General tab
go.fcmask	"Currency Decimals" as defined in System > Options > General tab
go.mrpmask	"MRP Decimals" as defined in System > Options > General tab
go.jobmask	Job number mask
go.purmask	Purchase Order number mask
go.ordmask	Sales Order number mask
go.actmask	Account Structure mask
go.emailsource	Default email source
go.finmask
go.username	First and Last Name of current User
go.timeout	Inactivity timeout minutes for current User
go.lastlogin	Last login of current User
go.grpnum	User Group ID assigned to current User
go.usernum	User ID of the current User
go.usin
go.purenter	User that entered Purchase Order
go.wlid	Whitelist ID
go.zoid	Zone ID
go.print	"Print To" selected on Copies To Print form
go.prtname	"Printer" selected on Copies To Print form

Scripting options

Beginning in version 15.03.052, it is now possible to use scripting in every expression field in the system. That means: lot number expression, work flow expression, user defined grid columns, triggers, report designer, user calcs, everywhere. With this change, Scripting can be used in every expression field in the system as long as df_expreng = Scripting or the expression's first line consists of #Engine=Scripting.

All available fields to an expression are available via the Fields object or the Globals object. For instance Globals.ordmask or Fields.pr_codenum. Related cursor fields are available via the cursor name, dot, and then the field name. This is the same as the classic expression engine. For instance: cUSERCALCS.u_calcname.

As a part of this, equality comparisons for DateTime objects have been implemented.

Backwards Compatibility Chart:

Legacy Function	Scripting Equivalent
ABS	Math.Abs
ALLT(RIM)	String.Trim
AT	String.IndexOf
BETWEEN	Utility.Between
BYTESTOSTRING	Convert.ByteStringToString
CAST	Standard casting with parenthesis or Convert functions
CDOW	DateTime.StringDayOfWeek
CHILDLOT	Inventory.ChildLot
DOW	DateTime.DayOfWeek
Ceiling	Math.Ceiling
CHR	Convert.UnicodeCharacter
CMONTH	DateTime.StringMonth
CONV24TO12	Utility.FormatTime
COUNTER	Use a variable and increment it
CTOD	new DateTime("12/25/2018")
DATE	DateTime.Today
DAY	DateTime.Day
DMY	Combination of DateTime fields
DOCEXISTS	Query dmpict, use Query.DocumentDatabase to determine the current docs db
DTOC	DateTime.ShortDateString
DTOS	DateTime.LongDateString
EMPTY	Utility.Empty
EVAL	Utility.EvaluateLegacyExpression
SQLVAL	Query
F_DATE	DateTime.Today
F_TIME	DateTime.Now
F_LOTVAL	Query
F_PASS	Utility.CheckSecurity
FLOOR	Math.Floor
GETVAR	Variables
IIF	If statement
INCREMENT	Utility.IncrementCounter
INLIST	N/A
ISALPHA	String.IsAlpha
ISDIGIT	String.IsDigit
GRIDSUM	Script.GetDataTable to gain access to the grid itself, then loop through it and calculate as necessary
LEFT	String.Left
LEN	Length property on the string
LIKE	String.IsRegexMatch
LOG	Math.Log
LOWER	String.ToLower
LTRIM	String.Ltrim
MAX	Math.Max
MEMLINES	String.Split
MIN	Math.Min
MLINE	String.Split
MOD	Modulus Operator (%)
MONTH	DateTime.Month
NVL	Ternary Operator (?:)
PADC	String.PadCenter
PADR	String.PadRight
PADL	String.PadLeft
PROPER	String.ToProper
RAT	String.LastIndexOf
RIGHT	String.Right
RECNO	N/A
ROUND	Math.Round
RTRIM	String.Rtrim
SETVAR	Variables
SPACE	PADR('', 10)
SQRT	Math.Sqrt
STR	Convert.ToString
STRTRAN	String.Replace
SUBSTR	String.SubString
TIME	Utility.FormatTime(DateTime.Now)
TRANS(FORM)	String.Transform
TTOD	DateTime.Date
TTOC	Utility.FormatTime
ADDHOURS	DateTime.AddHours
ADDMINUTES	DateTime.AddMinutes
ADDSECONDS	DateTime.AddSeconds
UPPER	String.ToUpper
USED	Script.GetDataTable will return null if the cursor does not exist
VAL	Convert.ToInteger
YEAR	DateTime.Year
F_FEATTEXT	Utility.GetFeatureText

Helpful expression examples

Purpose Need to grab text between two characters and have it print on a document.

Example text from user field picklist: GENERAL|Move out of dangerous area. Consult a physician. Show this safety sheet to the doctor in attendance.|EYE CONTACT|Corrosive to the eyes.

Expression below will grab just the bolded text from above. The only things that would need to be updated for users is the user field and at which pipe delimiter should it start and finish.

SUBSTR(u_first_aid, AT("|",u_first_aid,1)+1, AT("|",u_first_aid,2) - AT("|",u_first_aid,1)-1)

Purpose Add a Julian Date column to a report grid.

PADLstr((jo_planstart- DATE(YEAR(jo_planstart), 1, 1? + 1)),3,'0')+'-'+RIGHTstr(year(jo_planstart?),2)

or

Julian Date: <<ALLT(RIGHT(STR(YEAR(fi_lotdate),4,0),2)+PADL(ALLT(STR(fi_lotdate-DATE(YEAR(fi_lotdate),1,1)+1,3,0)),3,'0'))>> . This expression will generate in the YYDDD format. If you want the date in the YDDD format, use the following expression: Julian Date: <<ALLT(RIGHT(STR(YEAR(fi_lotdate),4,0),1)+PADL(ALLT(STR(fi_lotdate-DATE(YEAR(fi_lotdate),1,1)+1,3,0)),3,'0'))>>

cFormatCode Examples

• Convert chart of account number to string for display on reports or grids- TRANSFORM(ch_account, "9999-99-999") - example result - 130001001 will display as 1300-01-001. In this example we used a customized format to determine control how the digits display. An alternative specific to chart of accounts is to use go.actmask - example TRANSFORM(ch_account, go.actmask). This is desirable because it will use your specific Account Structure (via Acct> Maint > Acct Structure) - and does need need to be updated if the Account Structure is changed.